package cn.jhc.bookstore.cn.jhc.bookstore.db;

import cn.jhc.bookstore.beans.Book;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.List;

public class BookDao {

    private static final String CREATE_BOOK = "create table if not exists books(" +
            "id int auto_increment primary key," +
            "title varchar(255) not null," +
            "price decimal(10,2) default 0.0," +
            "pages int default 0," +
            "isbn char(13) not null," +
            "imagePath varchar(255))";
    private static final String DROP_TABLE_IF_EXISTS_BOOKS = "drop table if exists books";

    public static void createTable(QueryRunner runner) {
        try {
            runner.execute(DROP_TABLE_IF_EXISTS_BOOKS);
            runner.execute(CREATE_BOOK);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    public static void insertExampleData(QueryRunner runner) {
        InputStream inputStream = BookDao.class.getClassLoader().getResourceAsStream("insert_books.sql");
        if (inputStream != null) {
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
            String line = "";
            int count = 0;
            try {
                while ((line = bufferedReader.readLine()) != null) {
                    count = runner.insert(line.trim(), new ScalarHandler<>());
                }
            } catch (IOException e) {
                throw new RuntimeException(e);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            System.out.println("Inserted " + count + " rows in books table.");
        }
    }
    
    public static List<Book> queryAll(QueryRunner runner) {
        try {
            return runner.query("select * from books", new BeanListHandler<>(Book.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
